package ch.fhnw.kosmonauten.server.dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ch.fhnw.kosmonauten.model.Dossier;
import ch.fhnw.kosmonauten.model.Hexagram;

public class HexagramAccessLayer {
	private Statement s;
	private ResultSet rs;
	private Connection con;
	private Hexagram hex;
	
	public Hexagram getHexagram(Dossier dossier){
			
		
			Hexagram response = new Hexagram();
			int[] hexagrams = new int[5];
			try{
				con = DriverManager.getConnection(ConnectionString.url, ConnectionString.name, ConnectionString.passw);
				s = con.createStatement();
				rs = s.executeQuery("select h.HexagramID, h.nuclear, h.inverse, h.reverse, h.fuxi,st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname"
									+" from hexagrammapping hm"
									+" inner join hexagram h on hm.hexagramid = h.hexagramid"
									+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
									+" inner join user u on st.LanguageID = u.LanguageID"
									+" where hm.binarycode = '"+dossier.getBinarycode()+"' and u.UserID = "+dossier.getUserID()+";");
				
				if(rs.next()){
					response.setHexagramID(Integer.parseInt(rs.getString(1)));
					response.setNuclear(new Hexagram());
					response.getNuclear().setHexagramID(Integer.parseInt(rs.getString(2)));
					response.setInverse(new Hexagram());
					response.getInverse().setHexagramID(Integer.parseInt(rs.getString(3)));
					response.setReverse(new Hexagram());
					response.getReverse().setHexagramID(Integer.parseInt(rs.getString(4)));
					response.setFuxi(new Hexagram());
					response.getFuxi().setHexagramID(Integer.parseInt(rs.getString(5)));
					response.setLine1text(rs.getString(6));
					response.setLine2text(rs.getString(7));
					response.setLine3text(rs.getString(8));
					response.setLine4text(rs.getString(9));
					response.setLine5text(rs.getString(10));
					response.setLine6text(rs.getString(11));
					response.setJudge(rs.getString(12));
					response.setComment(rs.getString(13));
					response.setHexagramname(rs.getString(14));
					response.setBinarycode(dossier.getBinarycode());
					rs.close();
					s.close();
					s = con.createStatement();
					rs = s.executeQuery("select h.HexagramID, h.nuclear, h.inverse, h.reverse, h.fuxi,st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname"
							+" from hexagrammapping hm"
							+" inner join hexagram h on hm.hexagramid = h.hexagramid"
							+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
							+" inner join user u on st.LanguageID = u.LanguageID"
							+" where hm.binarycode = '"+dossier.getBinarycodePerspective()+"' and u.UserID = "+dossier.getUserID()+";");
					
					if(rs.next()){
						response.setPerspective(new Hexagram());
						response.getPerspective().setHexagramID(Integer.parseInt(rs.getString(1)));
						response.getPerspective().setLine1text(rs.getString(6));
						response.getPerspective().setLine2text(rs.getString(7));
						response.getPerspective().setLine3text(rs.getString(8));
						response.getPerspective().setLine4text(rs.getString(9));
						response.getPerspective().setLine5text(rs.getString(10));
						response.getPerspective().setLine6text(rs.getString(11));
						response.getPerspective().setJudge(rs.getString(12));
						response.getPerspective().setComment(rs.getString(13));
						response.getPerspective().setHexagramname(rs.getString(14));
						response.getPerspective().setBinarycode(dossier.getBinarycodePerspective());
						rs.close();
						s.close();
						
						s = con.createStatement();
						rs = s.executeQuery("select st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname, hm.Binarycode"
						+" from hexagram h" 
						+" inner join hexagrammapping hm on hm.hexagramid = h.hexagramid"
						+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
						+" inner join user u on st.LanguageID = u.LanguageID"
						+" where h.hexagramid = "+response.getNuclear().getHexagramID()+" and u.UserID = "+dossier.getUserID()+";");
						if(rs.next()){
							response.getNuclear().setLine1text(rs.getString(1));
							response.getNuclear().setLine2text(rs.getString(2));
							response.getNuclear().setLine3text(rs.getString(3));
							response.getNuclear().setLine4text(rs.getString(4));
							response.getNuclear().setLine5text(rs.getString(5));
							response.getNuclear().setLine6text(rs.getString(6));
							response.getNuclear().setJudge(rs.getString(7));
							response.getNuclear().setComment(rs.getString(8));
							response.getNuclear().setHexagramname(rs.getString(9));
							response.getNuclear().setBinarycode(rs.getString(10));
							rs.close();
							s.close();
							
							s = con.createStatement();
							rs = s.executeQuery("select st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname, hm.Binarycode"
							+" from hexagram h" 
							+" inner join hexagrammapping hm on hm.hexagramid = h.hexagramid"
							+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
							+" inner join user u on st.LanguageID = u.LanguageID"
							+" where h.hexagramid = "+response.getInverse().getHexagramID()+" and u.UserID = "+dossier.getUserID()+";");
							if(rs.next()){
								response.getInverse().setLine1text(rs.getString(1));
								response.getInverse().setLine2text(rs.getString(2));
								response.getInverse().setLine3text(rs.getString(3));
								response.getInverse().setLine4text(rs.getString(4));
								response.getInverse().setLine5text(rs.getString(5));
								response.getInverse().setLine6text(rs.getString(6));
								response.getInverse().setJudge(rs.getString(7));
								response.getInverse().setComment(rs.getString(8));
								response.getInverse().setHexagramname(rs.getString(9));
								response.getInverse().setBinarycode(rs.getString(10));
								rs.close();
								s.close();
								
								s = con.createStatement();
								rs = s.executeQuery("select st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname, hm.Binarycode"
								+" from hexagram h" 
								+ " inner join hexagrammapping hm on hm.hexagramid = h.hexagramid"
								+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
								+" inner join user u on st.LanguageID = u.LanguageID"
								+" where h.hexagramid = "+response.getFuxi().getHexagramID()+" and u.UserID = "+dossier.getUserID()+";");
								if(rs.next()){
									response.getFuxi().setLine1text(rs.getString(1));
									response.getFuxi().setLine2text(rs.getString(2));
									response.getFuxi().setLine3text(rs.getString(3));
									response.getFuxi().setLine4text(rs.getString(4));
									response.getFuxi().setLine5text(rs.getString(5));
									response.getFuxi().setLine6text(rs.getString(6));
									response.getFuxi().setJudge(rs.getString(7));
									response.getFuxi().setComment(rs.getString(8));
									response.getFuxi().setHexagramname(rs.getString(9));
									response.getFuxi().setBinarycode(rs.getString(10));
									rs.close();
									s.close();
									
									s = con.createStatement();
									rs = s.executeQuery("select st.Linetext1, st.Linetext2, st.Linetext3, st.Linetext4, st.Linetext5, st.Linetext6, st.Hexagramtextjudge, st.Hexagramtextcomment, st.Hexagramname, hm.Binarycode"
									+" from hexagram h" 
									+ " inner join hexagrammapping hm on hm.hexagramid = h.hexagramid"
									+" inner join systemTranslation st on  st.hexagramid = h.hexagramid"
									+" inner join user u on st.LanguageID = u.LanguageID"
									+" where h.hexagramid = "+response.getReverse().getHexagramID()+" and u.UserID = "+dossier.getUserID()+";");
									if(rs.next()){
										response.getReverse().setLine1text(rs.getString(1));
										response.getReverse().setLine2text(rs.getString(2));
										response.getReverse().setLine3text(rs.getString(3));
										response.getReverse().setLine4text(rs.getString(4));
										response.getReverse().setLine5text(rs.getString(5));
										response.getReverse().setLine6text(rs.getString(6));
										response.getReverse().setJudge(rs.getString(7));
										response.getReverse().setComment(rs.getString(8));
										response.getReverse().setHexagramname(rs.getString(9));
										response.getReverse().setBinarycode(rs.getString(10));
										rs.close();
										s.close();
										
										return response;
									}
									else
										return null;
								}
								else
									return null;
							}
							else
								return null;
						}
						else
							return null;
						
					}
					else
						return null;
				}
				else
					return null;
				
				
			}
			catch (Exception e){
				System.out.println( e.getMessage());
				return null;
			}finally {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
